from pymysql import *
from hashlib import *
from redis import *
from MongoDB.cMongoDB import MongoDBDemo


class MySQLDemo:
    def __init__(self, host='localhost', user='guest', password='guest', database='lz_py', port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.conn = None
        self.csr = None
        self.config = None

    def create_connection(self):
        self.config = {
            'host': self.host,
            'user': self.user,
            'password': self.password,
            'database': self.database,
            'port': self.port,
            'charset': self.charset,
        }
        self.conn = connect(**self.config)
        self.csr = self.conn.cursor()

    def fetch_one(self, sql='', param=''):
        self.csr.execute(sql, param)
        _result = self.csr.fetchone()
        return _result

    def insert_one(self, sql='', param=''):
        _count = self.csr.execute(sql, param)
        return _count

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def close_connection(self):
        self.csr.close()
        self.conn.close()

    def _test_register(self):
        """
        md = MySQLDemo(host="192.168.1.101")
        md._test_register()
        >
        lizhen
        lizhen
        """
        uname = input('请输入用户名：')
        upwd = input('请输入密码:')

        s1 = sha1()
        s1.update(upwd.encode())
        upwd_sha1 = s1.hexdigest()

        md = MySQLDemo(host=self.host)
        md.create_connection()
        pswd = md.fetch_one('select password from register where username=%s', [uname])
        if pswd:
            print("该用户名已被使用,注册失败")
        else:
            count = md.insert_one('insert into register values(%s, %s, %s)', [0, uname, upwd_sha1])
            md.conn.commit()
            if count == 1:
                print('注册成功')
            else:
                print('注册失败')
        md.close_connection()

    def _test_login(self, db_type='MySQL', name='', password=''):
        """
        md = MySQLDemo(host="192.168.1.101")
        md._test_login()
        >
        root
        root
        """
        if db_type == 'MySQL':
            uname = input('请输入用户名：')
            upwd = input('请输入密码:')

            s1 = sha1()
            s1.update(upwd.encode())
            upwd_sha1 = s1.hexdigest()
        elif db_type in ['MongoDB', 'Redis']:
            uname = name
            upwd_sha1 = password
        md = MySQLDemo(host=self.host)
        md.create_connection()
        pswd = md.fetch_one('select password from register where username=%s', [uname])
        if pswd is None:
            print("该用户名错误，登录失败")
        else:
            pwd = pswd[0]
            if pwd == upwd_sha1:
                print('登录成功')
                print('MySQL', uname)
                if db_type == 'MongoDB':
                    mgd = MongoDBDemo(host=self.host)
                    mgd.create_connection()
                    mgd.insert_one(collection='register', **{'username': uname, 'password': upwd_sha1})
                elif db_type == 'Redis':
                    sr = StrictRedis(host=self.host)
                    sr.set(uname, upwd_sha1)
            else:
                print('用户名或密码错误，登录失败')
        md.close_connection()

    def _test_login_mongodb(self):
        """
        md = MySQLDemo(host="192.168.1.101")
        md._test_login_mongodb()
        >
        root
        root
        """
        uname = input('请输入用户名：')
        upwd = input('请输入密码:')

        s1 = sha1()
        s1.update(upwd.encode())
        upwd_sha1 = s1.hexdigest()

        mgd = MongoDBDemo(host=self.host)
        mgd.create_connection()
        _result = mgd.find_one(collection='register', **{'username': uname})
        print(_result)
        if _result is None:
            md = MySQLDemo(host=self.host)
            md.create_connection()
            md._test_login(db_type='MongoDB', name=uname, password=upwd_sha1)
        else:
            if _result['password'] == upwd_sha1:
                print('登陆成功')
                print('MongoDB', uname)
            else:
                print('用户名或密码错误，登录失败')

    def _test_redis_login(self):
        """
        md = MySQLDemo(host="192.168.1.101")
        md._test_redis_login()
        >
        root
        root
        """
        uname = input('请输入用户名：')
        upwd = input('请输入密码:')

        s1 = sha1()
        s1.update(upwd.encode())
        upwd_sha1 = s1.hexdigest()

        sr = StrictRedis(host=self.host)
        _result = sr.get(uname)
        print(_result)
        if _result is None:
            md = MySQLDemo(host=self.host)
            md.create_connection()
            md._test_login(db_type='Redis', name=uname, password=upwd_sha1)
        else:
            if _result.decode() == upwd_sha1:
                print('登陆成功')
                print('Redis', uname)
            else:
                print('用户名或密码错误，登录失败')

    def _test_transaction(self):
        """
        md = MySQLDemo(host="192.168.1.101")
        md._test_transaction()
        >
        root
        root
        """
        try:
            md = MySQLDemo(host=self.host)
            md.create_connection()
            uname = 'test'
            s1 = sha1()
            s1.update('test'.encode())
            upwd_sha1 = s1.hexdigest()
            _count = md.insert_one('insert into register(username, password) values (%s, %s)', [uname, upwd_sha1])
            print(_count)
            md.commit()
        except Exception as e:
            md.rollback()
        finally:
            md.close_connection()


if __name__ == '__main__':
    md = MySQLDemo(host="192.168.1.101")
    md._test_transaction()
